package handledb;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Logger;

public class Pipeline {
    private static Logger log = Logger.getLogger(Pipeline.class.getSimpleName());
    private String url;
    private String user;
    private String passwd;
    private String driver;
    public String verifySql = "";
    volatile Connection con;
    volatile boolean hasFree = true;
    volatile long time = 0l;
    volatile long threadId = 0l;
    volatile long transactionTime = 0l;
    public long transactionId = 0l;
    int dbType = 0;
    public boolean noUpdate = true;

    private ResultSet rs = null;
    private Statement st = null;
    private PreparedStatement ps = null;

    public DbPool dbPool;

    public String[] selectFields;
    public String[] unSelectFields;
    public String[] updateFields;
    public String[] notUpdateFields;
    public String[] whereFields;

    public void clearFields() {
        selectFields = null;
        unSelectFields = null;
        updateFields = null;
        notUpdateFields = null;
        whereFields = null;
    }

    private boolean showQuery = false;
    private boolean showUpdate = false;
    private int tempNum;
    public boolean distributedTransaction = false;

    Pipeline(String url, String user, String passwd, String driver, int type, String verifySql, boolean showQuery, boolean showUpdate) {
        super();
        this.url = url;
        this.user = user;
        this.passwd = passwd;
        this.driver = driver;
        this.dbType = type;
        this.verifySql = verifySql;
        this.showUpdate = showUpdate;
        this.showQuery = showQuery;
        createConnection();
    }

    Pipeline(PoolConfig config, DbPool dbPool) {
        super();
        this.url = config.url;
        this.user = config.user;
        this.passwd = config.passwd;
        this.driver = config.driver;
        this.dbType = config.dbType;
        this.verifySql = config.verifySql;
        this.showUpdate = config.showUpdate;
        this.showQuery = config.showQuery;
        this.dbPool = dbPool;
        createConnection();
    }

    // 创建数据库连接通道
    private void createConnection() {
        try {
            if (con != null) {
                con.close();
            }
            con = DriverManager.getConnection(url, user, passwd);
            con.setAutoCommit(false);//关闭自动提交
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }

    // 验证连接是否可用
    public boolean testConnection() {
        try {// 判断连接可不可用
            if (con.isValid(1)) {
                con.setAutoCommit(false);//关闭自动提交
            } else if (verifySql != null) {
                Statement stmt = con.createStatement();
                stmt.execute(verifySql);
                con.setAutoCommit(false);//关闭自动提交
            } else {
                createConnection();
                return false;
            }
        } catch (SQLException e) {
            System.err.println("The connection is unavailable. Close the connection and reconnect!");
            createConnection();
            return false;
        }
        return true;
    }

    public void commit() throws SQLException {
        con.commit();
        noUpdate = true;
    }

    public void rollback() throws SQLException {
        con.rollback();
        noUpdate = true;
    }

    public void close() throws SQLException {
        con.close();
    }

    private void clear() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (ps != null) {
                ps.close();
                ps = null;
            }
            if (st != null) {
                st.close();
                st = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private String pageSqlDeal(String sql, int pageNo, int pageSize) {
        if (dbType == 2) {
            return sql + " limit " + pageSize * (pageNo - 1) + "," + pageSize;
        } else if (dbType == 1) {
            int lastOderbyIndex = sql.lastIndexOf("order by");
            int lastWhereIndex = sql.lastIndexOf("where");
            int lastFromIndex = sql.lastIndexOf("from");
            String numStr = " and rownum<" + pageNo * pageSize;
            if (lastWhereIndex < lastFromIndex && sql.lastIndexOf(')') < lastFromIndex) {
                numStr = " where rownum<" + pageNo * pageSize;
            }
            if (lastOderbyIndex > -1) {
                return "select * from (select rownum," + sql.substring(7, lastOderbyIndex) + numStr + sql.substring(lastOderbyIndex - 1) + ") where rownum>" + pageSize * (pageNo - 1);
            } else {
                return "select * from (select rownum," + sql.substring(7) + numStr + ") where rownum>" + pageSize * (pageNo - 1);
            }
        }
        return sql;
    }

    // 实体类查询SQL组装
    private String assemblySelectSql(ClassInfo classInfo) {
        StringBuffer sb = new StringBuffer("select ");
        boolean hasNext;
        if (unSelectFields != null && unSelectFields.length > 0) {
            String str = "";
            for (FieldInfo info : classInfo.allFieldInfos) {
                hasNext = true;
                str = info.name;
                for (String s : unSelectFields) {
                    if (str.equals(s)) {
                        hasNext = false;
                        break;
                    }
                }
                if (hasNext) {
                    if (info.relationSql != null) {
                        sb.append(info.relationSql).append(",");
                    } else {
                        sb.append('`').append(info.columnName).append("`,");
                    }
                }
            }
            unSelectFields = null;
            return sb.substring(0, sb.length() - 1);
        } else if (selectFields != null && selectFields.length > 0) {
            String str = "";
            for (FieldInfo info : classInfo.allFieldInfos) {
                str = info.name;
                for (String s : selectFields) {
                    if (str.equals(s)) {
                        if (info.relationSql != null) {
                            sb.append(info.relationSql).append(",");
                        } else {
                            sb.append('`').append(info.columnName).append("`,");
                        }
                        break;
                    }
                }
            }
            selectFields = null;
            return sb.substring(0, sb.length() - 1);
        } else {
            sb.append("*,");
            for (FieldInfo info : classInfo.allFieldInfos) {
                if (info.relationSql != null) {
                    sb.append(info.relationSql).append(",");
                }
            }
            return sb.substring(0, sb.length() - 1);
        }
    }

    private StringBuffer setArrayParams(StringBuffer sql, FieldInfo info, Object param, List<Object> params) {
        if (param == null) {
            return sql;
        }
        Object[] arrs = null;
        if (info.type >= 20 && info.type < 100) {
            arrs = (Object[]) param;
        } else if (info.type == 100) {
            arrs = ((List<Object>) param).toArray();
        }
        StringBuffer str = new StringBuffer();
        if (arrs != null && arrs.length > 0) {
            for (Object obj : arrs) {
                str.append("?,");
                params.add(obj);
            }
            int n = info.conditionSql.indexOf('?');
            sql.append(info.conditionSql.substring(0, n) + str.substring(0, str.length() - 1) + ")");
        }
        return sql;
    }

    private StringBuffer setParams(StringBuffer sql, FieldInfo info, Object param, List<Object> params, boolean hasCondition) {
        if (hasCondition && info.type >= 20) {
            return setArrayParams(sql, info, param, params);
        }
        String str = "?";
        if (info.type == 4 && param != null) {
            SimpleDateFormat formatter = new SimpleDateFormat(info.format);
            if (dbType == 2) {
                str = "STR_TO_DATE(?,'" + info.mysqlFormat + "')";
            } else {
                str = "TO_DATE(?,'" + info.format + "')";
            }
            params.add(formatter.format((Date) param));
        } else {
            if (hasCondition && info.like != null) {
                params.add(info.like.replaceAll(info.name, param.toString()));
            } else {
                params.add(param);
            }
        }
        if (hasCondition) {
            if (info.conditionSql != null) {
                if (info.type == 4) {
                    tempNum = info.conditionSql.indexOf("?");
                    sql.append(info.conditionSql.substring(0, tempNum)).append(str);
                } else {
                    sql.append(info.conditionSql);
                }
            } else if (info.like != null) {
                tempNum = sql.length();
                sql.delete(tempNum - 1, tempNum);
                sql.append(" like ?");
            } else {
                sql.append(str);
            }
        } else {
            sql.append(str);
        }
        return sql;
    }

    // 一般查询条件sql组装
    private <T> String whereCondition(T t, ClassInfo classInfo, List<Object> params) {
        StringBuffer condition = new StringBuffer();
        Object obj;
        try {
            if (whereFields != null) {
                for (String str : whereFields) {
                    for (FieldInfo info : classInfo.allFieldInfos) {
                        if (str.equals(info.name)) {
                            obj = info.field.get(t);
                            if (obj == null) {
                                break;
                            }
                            if (info.conditionSql == null) {
                                condition.append(" and `").append(info.columnName).append("`=");
                            }
                            setParams(condition, info, obj, params, true);
                            break;
                        }
                    }
                }
                whereFields = null;
            } else {
                for (FieldInfo info : classInfo.allFieldInfos) {
                    obj = info.field.get(t);
                    if (!(obj == null || "".equals(obj))) {
                        if (info.conditionSql == null) {
                            condition.append(" and `").append(info.columnName).append("`=");
                        }
                        setParams(condition, info, obj, params, true);
                    }
                }
            }
            if (condition.length() > 0) {
                return " where" + condition.substring(4);
            }
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        return "";
    }

    // 跟新数据条件sql组装
    private String updateCondition(Object arg, List<Object> params, ClassInfo classInfo) throws IllegalAccessException {
        StringBuffer condition = new StringBuffer();
        FieldInfo info = classInfo.allFieldInfos[0];
        Object obj = info.field.get(arg);
        if (obj != null) {
            if (info.conditionSql == null) {
                condition.append(" and `").append(info.columnName).append("`=");
            }
            setParams(condition, info, obj, params, true);
        }
        if (whereFields != null) {
            for (String str : whereFields) {
                for (FieldInfo info1 : classInfo.allFieldInfos) {
                    if (str.equals(info1.name)) {
                        obj = info1.field.get(arg);
                        if (obj == null) {
                            break;
                        }
                        if (info1.conditionSql == null) {
                            condition.append(" and `").append(info1.columnName).append("`=");
                        }
                        setParams(condition, info1, obj, params, true);
                        break;
                    }
                }
            }
            whereFields = null;
        }
        if(condition.length()<4){// update和delete语句限制必须带条件
            throw new RuntimeException("no conditions are set with the new statement");
        }
        return condition.substring(4);
    }

    public ResultMap queryToMap(String sql, Object... params) {
        try {
            ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
            return new ResultMap(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            clear();
        }
        return new ResultMap();
    }

    public ResultSet query(String sql, Object... params) throws SQLException {
        if (showQuery) {
            log.info("query: " + sql);
        }
        ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }
        rs = ps.executeQuery();
        return rs;
    }

    private <E> E queryToObject(String sql, List<Object> params, ClassInfo classInfo) {
        try {
            return (E) ResultSetHandle.resultSetToObject(query(sql, params.toArray()), classInfo);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> T queryToObject(String sql, ClassInfo classInfo, Object... params) {
        try {
            return (T) ResultSetHandle.resultSetToObject(query(sql, params), classInfo);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> T queryToObject(T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = assemblySelectSql(classInfo) + " from " + classInfo.tableName + whereCondition(t, classInfo, params);
        return queryToObject(sql, params, classInfo);
    }

    public <T> List<T> queryToList(String sql, ClassInfo classInfo, Object... params) {
        try {
            return ResultSetHandle.resultSetToList(query(sql, params), classInfo);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> List<T> queryToList(T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = assemblySelectSql(classInfo) + " from " + classInfo.tableName + whereCondition(t, classInfo, params);
        return queryToList(sql, classInfo, params.toArray());
    }

    public String queryToJson(String sql, ClassInfo c, Object... params) {
        try {
            return ResultSetHandle.resultSetToJson(query(sql, params), c);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public String queryToJson(String sql, Object... params) {
        try {
            return ResultSetHandle.tojson(query(sql, params));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> String queryToJson(T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = assemblySelectSql(classInfo) + " from " + classInfo.tableName + whereCondition(t, classInfo, params);
        try {
            return ResultSetHandle.resultSetToJson(query(sql, params.toArray()), classInfo);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> List<T> queryToList(T t, ClassInfo classInfo, int pageNo, int pageSize) {
        List<Object> params = new ArrayList<>();
        String sql = null;
        if (dbType == 2) { // mysql分页
            sql = assemblySelectSql(classInfo) + " from " + classInfo.tableName + whereCondition(t, classInfo, params) + " limit " + pageSize * (pageNo - 1) + "," + pageSize;
        } else if (dbType == 1) {
            String where = whereCondition(t, classInfo, params);
            if ("".equals(where)) {
                where = " where rownum<" + pageNo * pageSize;
            } else {
                where += " and rownum<" + pageNo * pageSize;
            }
            sql = "select * from (" + assemblySelectSql(classInfo) + ",rownum from " + classInfo.tableName + where + ") where rownum>" + pageSize * (pageNo - 1);
        }
        return queryToList(sql, classInfo, params.toArray());
    }

    public int updateBySql(String sql, Object... params) {
        if (showUpdate) {
            log.info("updateBySql: " + sql);
        }
        try {
            ps = con.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            if(noUpdate){
                noUpdate = false;
            }
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> int update(T t, ClassInfo classInfo) {
        StringBuffer sql = new StringBuffer("update ").append(classInfo.tableName).append(" set ");
        List<Object> params = new ArrayList<>();
        Object obj;
        int i = -1;
        try {
            if (updateFields != null) {
                for (FieldInfo info : classInfo.allFieldInfos) {
                    i++;
                    if (i == 0 || !info.updateField) {
                        continue;
                    }
                    for (String str : updateFields) {
                        if (str.equals(info.name)) {
                            obj = info.field.get(t);
                            if(obj==null){
                                continue;
                            }
                            sql.append("`").append(info.columnName).append("`=");
                            setParams(sql, info, obj, params, false);
                            sql.append(",");
                        }
                    }
                }
                updateFields = null;
            } else if (notUpdateFields != null) {
                boolean hasNext = true;
                for (FieldInfo info : classInfo.allFieldInfos) {
                    i++;
                    if (i == 0 || !info.updateField) {
                        continue;
                    }
                    hasNext = true;
                    for (String str : notUpdateFields) {
                        if (str.equals(info.name)) {
                            hasNext = false;
                            break;
                        }
                    }
                    if (hasNext) {
                        obj = info.field.get(t);
                        if(obj==null){
                            continue;
                        }
                        sql.append("`").append(info.columnName).append("`=");
                        setParams(sql, info, obj, params, false);
                        sql.append(",");
                    }
                }
                notUpdateFields = null;
            } else {
                for (FieldInfo info : classInfo.allFieldInfos) {
                    i++;
                    if (i == 0 || !info.updateField) {
                        continue;
                    }
                    obj = info.field.get(t);
                    if(obj==null){
                        continue;
                    }
                    sql.append("`").append(info.columnName).append("`=");
                    setParams(sql, info, obj, params, false);
                    sql.append(",");
                }
            }
            String str = sql.substring(0, sql.length() - 1) + " where" + updateCondition(t, params, classInfo);
            return updateBySql(str, params.toArray());
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    public <T> int delete(T t, ClassInfo classInfo) {
        try {
            List<Object> params = new ArrayList<>();
            String sql = "delete from " + classInfo.tableName + " where " + updateCondition(t, params, classInfo);
            return updateBySql(sql, params.toArray());
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    public <T> int create(T t, ClassInfo classInfo) {
        try {
            StringBuffer insertSql = new StringBuffer("insert into ").append(classInfo.tableName).append("(");
            StringBuffer values = new StringBuffer(" values(");
            List<Object> params = new ArrayList<>();
            Object obj;
            for (FieldInfo info : classInfo.allFieldInfos) {
                if (info.updateField) {
                    obj = info.field.get(t);
                    if (obj != null) {
                        insertSql.append("`").append(info.columnName).append("`,");
                        setParams(values, info, obj, params, false);
                        values.append(",");
                    }
                }
            }
            String sql = insertSql.substring(0, insertSql.length() - 1) + ")" + values.substring(0, values.length() - 1) + ")";
            return updateBySql(sql, params.toArray());
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
    }

    public int queryToInt(String sql, Object... objects) {
        try {
            query(sql, objects);
            if (rs.next()) {
                return rs.getInt(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> int sumByObject(T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = "select count(*) from " + classInfo.tableName + whereCondition(t, classInfo, params);
        return queryToInt(sql, params.toArray());
    }

    public String queryToString(String sql, Object... params) {
        try {
            query(sql, params);
            if (rs.next()) {
                return rs.getString(1);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public List<String> queryToListStr(String sql, Object... params) {
        try {
            query(sql, params);
            List<String> strs = new ArrayList();
            while (rs.next()) {
                strs.add(rs.getString(1));
            }
            return strs;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            clear();
        }
    }

    public <T> int templateUpdate(String tempSql, T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = SqlResolver.templateResolver(tempSql, t, params, classInfo);
        // update delete 语句必须带where条件
        if((sql.indexOf("update")>-1 || sql.indexOf("delete")>-1 || sql.indexOf("UPDATE")>-1 || sql.indexOf("DELETE")>-1) && !(sql.indexOf("where")>-1 || sql.indexOf("WHERE")>-1)){
            throw new RuntimeException("no conditions are set with the new statement");
        }
        return updateBySql(sql, params.toArray());
    }

    public <T> T templateQueryOne(String tempSql, T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = SqlResolver.templateResolver(tempSql, t, params, classInfo);
        return queryToObject(sql, classInfo, params.toArray());
    }

    public <T, M> T templateQueryOne(String tempSql, M m, ClassInfo paramInfo, ClassInfo resultInfo) {
        List<Object> params = new ArrayList<>();
        String sql = SqlResolver.templateResolver(tempSql, m, params, paramInfo);
        return queryToObject(sql, resultInfo, params.toArray());
    }

    public <T> List<T> templateQueryAll(String tempSql, T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = SqlResolver.templateResolver(tempSql, t, params, classInfo);
        return queryToList(sql, classInfo, params.toArray());
    }

    public <T> List<T> templateQueryAll(String tempSql, T t, ClassInfo classInfo, int pageNo, int pageSize) {
        List<Object> params = new ArrayList<>();
        String sql = pageSqlDeal(SqlResolver.templateResolver(tempSql, t, params, classInfo), pageNo, pageSize);
        return queryToList(sql, classInfo, params.toArray());
    }

    public <T, M> List<T> templateQueryAll(String tempSql, M m, ClassInfo paramInfo, ClassInfo resultInfo) {
        List<Object> params = new ArrayList<>();
        String sql = SqlResolver.templateResolver(tempSql, m, params, paramInfo);
        return queryToList(sql, resultInfo, params.toArray());
    }

    public <T, M> List<T> templateQueryAll(String tempSql, M m, ClassInfo paramInfo, ClassInfo resultInfo, int pageNo, int pageSize) {
        List<Object> params = new ArrayList<>();
        String sql = pageSqlDeal(SqlResolver.templateResolver(tempSql, m, params, paramInfo), pageNo, pageSize);
        return queryToList(sql, resultInfo, params.toArray());
    }

    public <T> int templateCount(String tempSql, T t, ClassInfo classInfo) {
        List<Object> params = new ArrayList<>();
        String sql = SqlResolver.templateResolver(tempSql, t, params, classInfo);
        return queryToInt(sql, params);
    }

    public <T> String templateResolver(String tempSql, T t, List<Object> params, ClassInfo classInfo) {
        return SqlResolver.templateResolver(tempSql, t, params, classInfo);
    }
}
